package mof.gov.dmdc.view.common;

import java.io.IOException;
import java.io.OutputStream;

import java.io.PrintWriter;

import java.math.BigDecimal;

import java.text.DateFormat;

import java.text.SimpleDateFormat;

import javax.faces.context.ExternalContext;

import javax.faces.context.FacesContext;

import javax.servlet.http.HttpServletResponse;

import oracle.adf.model.binding.DCIteratorBinding;

import oracle.jbo.AttributeDef;
import oracle.jbo.AttributeHints;
import oracle.jbo.Row;
import oracle.jbo.RowSetIterator;
import oracle.jbo.ViewObject;
import oracle.jbo.domain.Date;
import oracle.jbo.domain.Number;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

public class ExportUtils {
    public ExportUtils() {
        super();
    }
    private static TextConverter converter = new TextConverter();

    private static String SEARCH_ITERATOR_NAME = "searchVOIterator";
    private static String EXPORT_DEF_ITERATOR_NAME = "exportDEFVOIterator";

    public static DCIteratorBinding getSearchIteratorBinding() {
        return Common.findIterator(SEARCH_ITERATOR_NAME);
    }

    public static DCIteratorBinding getExportDefIteratorBinding() {
        return Common.findIterator(EXPORT_DEF_ITERATOR_NAME);
    }

    public static void createXLS(RowSetIterator iter, AttributeDef[] attrDefs, String[] attrs, long maxRowCount, String fileName, boolean isTCVN) {
        ExternalContext externalContext = FacesContext.getCurrentInstance().getExternalContext();
        HttpServletResponse response = (HttpServletResponse)externalContext.getResponse();
        try {
            OutputStream outputStream = response.getOutputStream();
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment; filename = \"" + fileName + "\"");
            response.setHeader("Cache-Control", "max-age=0");
            HSSFWorkbook wordbook = createWorkbook(iter, attrDefs, attrs, maxRowCount, isTCVN);
            wordbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
            response.flushBuffer();
            FacesContext.getCurrentInstance().responseComplete();
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("Error here: mof.gov.dmdc.view.common.ExportUtils.java -- createXLS()");
        }
    }

    public static void createTXT(RowSetIterator iter, AttributeDef[] attrDefs, String[] attrs, long maxRowCount, String fileName,
                                 boolean isTCVN) throws IOException {

        //********************** Prepare response ******************************/
        ExternalContext ectx = FacesContext.getCurrentInstance().getExternalContext();
        HttpServletResponse response = (HttpServletResponse)ectx.getResponse();
        if (!isTCVN) {
            response.setContentType("text/plain;charset=utf-8");
        } else {
            response.setContentType("text/plain;charset=windows-1252");
            //response.setContentType("text/plain;charset=utf-8");
        }
        response.setHeader("Content-disposition", "attachment; filename=" + fileName);

        //--------------------------Render header -------------------------//
        PrintWriter out = response.getWriter();
        out.write("#Version: DMDC 4.0");
        out.println();
        java.util.Date currenDate = new java.util.Date();
        SimpleDateFormat dateFormater = new SimpleDateFormat("dd/MM/yyyy");
        String date = dateFormater.format(currenDate);
        out.print("#Date: " + date);
        out.println();
        out.write("#Name: " + getTableFullName());
        out.println();
        out.write("#RECORDS: " + maxRowCount);
        out.println();
        out.write("#STRUCTURE: ");
        out.println();
        for (int i = 0; i < attrDefs.length; i++) {
            //String label = attrDefs[i].getUIHelper().getLabel(Common.getLocaleContext());
            String label = attrDefs[i].getName();
            label = isTCVN ? converter.convertUtf8ToTcvn3(label) : label;
            out.write("#" + label);
            out.println();
        }
        out.write("#");
        out.println();
        long idx = 0;

        //------------------------------Render all Rows-----------------------------//
        while (iter.hasNext() && idx < maxRowCount) {
            Row r = (idx == 0) ? iter.getCurrentRow() : iter.next();
            for (int i = 0; i < attrs.length; i++) {
                Object value = r.getAttribute(attrs[i]);
                String stringValue = "null";
                if (value != null) {
                    if (value instanceof Date) {
                        stringValue = converter.convertDate2String((Date)value);
                    } else {
                        stringValue = isTCVN ? converter.convertUtf8ToTcvn3(value.toString()) : value.toString();
                    }
                } else {
                    stringValue = "";
                }
                out.write(stringValue + ";");
            }
            out.println();
            idx++;
        }
        out.flush();
        out.close();
        response.flushBuffer();
        FacesContext.getCurrentInstance().responseComplete();
    }

    public static void createXML(RowSetIterator iter, String[] attrs, long maxRowCount, String fileName, boolean isTCVN) throws IOException {

        //-------------------------- prepare response -------------------------------//
        ExternalContext ectx = FacesContext.getCurrentInstance().getExternalContext();
        HttpServletResponse response = (HttpServletResponse)ectx.getResponse();
        if (!isTCVN) {
            response.setContentType("text/plain;charset=UTF-8");
        } else {
            response.setContentType("text/plain;charset=windows-1252");
        }
        response.setHeader("Content-disposition", "attachment; filename=" + fileName);

        //-------------------------- render xml content ----------------------------//
        PrintWriter out = response.getWriter();
        String tabString = "     ";
        if (isTCVN) {
            out.write("<?xml version='1.0' encoding='windows-1252' ?>");
        } else {
            out.write("<?xml version='1.0' encoding='UTF-8' ?>");
        }
        out.println();
        out.write("<" + getTableFullName() + ">");
        out.println();
        long idx = 0;
        while (iter.hasNext() && idx < maxRowCount) {
            Row r = (idx == 0) ? iter.getCurrentRow() : iter.next();
            out.write(tabString + "<RECORD>");
            out.println();

            for (int i = 0; i < attrs.length; i++) {
                out.write(tabString + tabString + "<" + attrs[i] + ">");
                Object value = r.getAttribute(attrs[i]);
                String stringValue = "";
                if (value != null) {
                    if (value instanceof Date) {
                        stringValue = converter.convertDate2String((Date)value);
                    } else {
                        stringValue = isTCVN ? converter.convertUtf8ToTcvn3(value.toString()) : value.toString();
                    }
                }
                out.write(stringValue);
                out.write("</" + attrs[i] + ">");
                out.println();
            }
            out.write(tabString + "</RECORD>");
            out.println();
            idx++;
        }
        out.write("</" + getTableFullName() + ">");
        out.flush();
        out.close();
        response.flushBuffer();
        FacesContext.getCurrentInstance().responseComplete();
    }

    private static HSSFWorkbook createWorkbook(RowSetIterator iter, AttributeDef[] attr, String[] attributes, long maxrowcount,
                                               boolean isTCVN) throws IOException {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("Dmdc4.0");
        int idx = 0; // rows index (idx = 0) ~ 'header row'
        HSSFRow row = sheet.createRow(idx);

        //Create boldFont
        HSSFFont boldFont = wb.createFont();
        boldFont.setColor(HSSFColor.BLUE_GREY.index);
        boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        boldFont.setFontHeightInPoints((short)13);

        //Create bold style for header
        HSSFCellStyle boldStyle = wb.createCellStyle();
        boldStyle.setFont(boldFont);
        boldStyle.setFillBackgroundColor(HSSFColor.GREY_50_PERCENT.index);

        //Create normal style for normal row
        HSSFCellStyle style = wb.createCellStyle();
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)"));
        style.setFillBackgroundColor(HSSFColor.DARK_BLUE.index);
        style.setFillPattern(HSSFCellStyle.BIG_SPOTS);


        /******************************** Creating headers ************************************/
        for (int i = 0; i < attr.length; i++) {
            AttributeHints hints = attr[i].getUIHelper();
            String label = hints.getLabel(Common.getLocaleContext());
            label = isTCVN ? converter.convertUtf8ToTcvn3(label) : label;
            row.createCell(i).setCellValue(label);
            row.getCell(i).setCellStyle(boldStyle);
        }
        idx++;

        /******************************* Creating first row **************************************/
        Row fr = iter.getCurrentRow();
        long rowcount = 1;
        row = sheet.createRow(idx);
        /******************************** Creating cells *************************************/
        for (int j = 0; j < attributes.length; j++) {
            HSSFCell cell = row.createCell(j);
            Object value = fr.getAttribute(attributes[j]);
            setConvertedCellValue(cell, value, isTCVN);
        }
        idx++;
        /******************************** Creating rows ****************************************/
        while (iter.hasNext() && ++rowcount <= maxrowcount) {
            Row r = iter.next();
            row = sheet.createRow(idx);
            /******************************** Creating cels *************************************/
            for (int j = 0; j < attributes.length; j++) {
                HSSFCell cell = row.createCell(j);
                Object value = r.getAttribute(attributes[j]);
                setConvertedCellValue(cell, value, isTCVN);
            }
            idx++;
        }
        for (int i = 0; i < attributes.length; i++) {
            sheet.autoSizeColumn(i);
        }

        return wb;
    }

    private static void setConvertedCellValue(HSSFCell cell, Object value, boolean isTCVN) {

        if (value instanceof Number) {
            Number number = (Number)value;
            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            cell.setCellValue(number.getValue());
        }

        if (value instanceof BigDecimal) {
            BigDecimal number = (BigDecimal)value;
            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            cell.setCellValue(number.longValue());
        }

        if (value instanceof Date) {
            Date adfdate = (Date)value;
            java.util.Date date = adfdate.getValue();
            DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
            String dateString = dateFormat.format(date).toString();
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(dateString);
        }

        if (value instanceof String) {
            String string = (String)value;
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            string = isTCVN ? converter.convertUtf8ToTcvn3(string) : string;
            cell.setCellValue(string);
        }
    }

    public static String getTableName() {
        ViewObject vo = getSearchIteratorBinding().getViewObject();
        String query = vo.getQuery();
        int a = query.lastIndexOf("FROM");
        int b = query.lastIndexOf("ORDER") == -1 ? query.lastIndexOf(")") : query.lastIndexOf("ORDER");
        String strReturn = query.substring(a + 4, b).trim();
        if (strReturn.indexOf("DM_DVQHNS")>=0){
            strReturn = "DM_DVQHNS";
        }   
        if (strReturn.indexOf("DM_DBHC")>=0){
            strReturn = "DM_DBHC";
        } 
        if (strReturn.indexOf("DM_MST_CANHAN")>=0 || strReturn.indexOf("DB_DTNT")>=0){
            strReturn = "DM_DTNT";
        }
        return strReturn;
    }
    public static String getTableFullName() {
        ViewObject vo = getSearchIteratorBinding().getViewObject();
        String query = vo.getQuery();
        int a = query.lastIndexOf("FROM");
        int b = query.lastIndexOf("ORDER") == -1 ? query.lastIndexOf(")") : query.lastIndexOf("ORDER");
        String strReturn = query.substring(a + 4, b).trim();
        return strReturn;
    }
}
